/**
 * sql增删改查
 * 增
 * INSERT INTO 表名(字段1，字段2) VALUES("值1"，"值2");
 * 删
 * DELETE FROM 表名 条件语句
 * 改
 * UPDATE 表名 SET 字段="值" 条件语句
 * 查
 * SELECT * FROM 表名 条件语句
 */

/**
 * sql查询语句 WHERE(条件)
 * ><=
 * SELECT * FROM users WHERE age=20;
 *
 * AND OR
 * SELECT * FROM users WHERE age=20 OR age=21;
 *
 * ORDER BY （排序ASC DESC）
 * SELECT * FROM users ORDER BY age ASC;
 *
 * LIMIT 分页相关,限制
 * SELECT * FROM users LIMIT 0,5
 *
 * LIKE
 * SELECT * FROM users WHERE username LIKE "李%";
 *
 * AS JOIN ON 联表查询
 * SELECT * FROM products AS p LEFT JOIN users AS u ON p.uid = u.id;
 */

const Koa = require('koa');
const Router = require('koa-router');
const mysql2 = require('mysql2');

const app = new Koa();
const router = new Router();

const connection = mysql2.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'root',
  database: 'test'
});

router.get('/', (ctx, next) => {
  ctx.redirect('/index');
})

router.get('/index', async (ctx, next) => {
  let [rows, fields] = await connection.promise().query(`SELECT u.id, u.name, u.age, p.pname FROM users AS u RIGHT JOIN products AS p ON u.id = p.uid;`);

  ctx.body = rows;
})

router.get('/details', (ctx, next) => {
  ctx.body = '详细页面';
})

router.get('/test', (ctx, next) => {
  ctx.body = {
    name: '张三',
    age: 20
  };
})

app.use(router.routes());

app.listen(8888);